package main.java.org.dss.lm.admin.dao.admin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.StringTokenizer;

import main.java.org.dss.lm.admin.bean.UserBean;
import main.java.org.dss.lm.admin.common.ITSConstants;
import main.java.org.dss.lm.admin.connection.DatabaseConnection;
import main.java.org.dss.lm.admin.util.LocMgmtUtil;

public class UserService implements IUserService {
	
	DatabaseConnection lmDB = new DatabaseConnection();

    Connection con;
    PreparedStatement pst1 = null;
    ResultSet rs1 = null;
    Integer i = 0;
    long autoUserId;
	
	public long getAutoUserId()
    {
        try{
            con=lmDB.getDBConnection();
            pst1 = con.prepareStatement("select max(UserID)+1 from user");
            rs1 = pst1.executeQuery();
            rs1.next();
            if(rs1.getInt(1)==0) autoUserId = 1;
            else autoUserId = rs1.getInt(1);            
        }
        catch(Exception e){e.printStackTrace();}
        finally{try{if(pst1!=null)pst1.close();if(rs1!=null)rs1.close();if(con!=null)con.close();}catch(SQLException ee){}}
        return autoUserId;
    }
	
	
	 public String addUser(UserBean userBean) throws Exception {
		autoUserId=getAutoUserId();
        try
        {
        //	if(userBean.get)
            con=lmDB.getDBConnection();
            pst1=con.prepareStatement("insert into user(UserID, JameInsanID, InsanID, Name, Adress, Block, District, State, Country, ContactNumber, RoleID, Password, Status) values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
            pst1.setLong(1,autoUserId);
            pst1.setLong(2,Long.valueOf(userBean.getJiid()));
            pst1.setLong(3,Long.valueOf(userBean.getInsanId()));
            pst1.setString(4,userBean.getName());
            pst1.setString(5,userBean.getAddress());
            pst1.setString(6,userBean.getBlock());
            pst1.setString(7,userBean.getDistrict());
            pst1.setString(8,userBean.getState());
            pst1.setString(9,userBean.getCountry());
            if(LocMgmtUtil.isNullOrBlankOrSpeltSo(userBean.getContactNumber()))
            	pst1.setLong(10, 0);
            else
            	pst1.setLong(10,Long.valueOf(userBean.getContactNumber()));
            
            pst1.setInt(11,Integer.valueOf(userBean.getRole()));
            pst1.setString(12,ITSConstants.DEFAULT_PASSWORD);
            pst1.setString(13,userBean.getStatus());
            pst1.executeUpdate();
            setGroupAccessToUser(con,userBean, autoUserId);
        }
        catch(Exception e){e.printStackTrace();}
        finally{try{if(pst1!=null)pst1.close();if(rs1!=null)rs1.close();if(con!=null)con.close();}catch(SQLException ee){}}
        
        try
        {
            con=lmDB.getDBConnection();
            pst1=con.prepareStatement("insert into user_role(UserID, RoleID) values(?,?)");
            pst1.setLong(1,autoUserId);
            pst1.setInt(2,Integer.valueOf(userBean.getRole()));        
            pst1.executeUpdate();
        }
        catch(Exception e){e.printStackTrace();}
        finally{try{if(pst1!=null)pst1.close();if(rs1!=null)rs1.close();if(con!=null)con.close();}catch(SQLException ee){}}
        
        return ITSConstants.SUCCESS;
    }
	
	 private void setGroupAccessToUser(Connection con, UserBean bean, long userId){
		 System.out.println("role ----- "+bean.getRole());
		 System.out.println("getSelectedGroups  "+bean.getSelectedGroups());
		PreparedStatement ps;
		String query="insert into location_user (UserID, GroupID) values (?,?)";
		String groupId=null;
		if("1".equals(bean.getRole()) || "2".equals(bean.getRole())){
			/*System.out.println("Hi");
			StringTokenizer token = new StringTokenizer(bean.getSelectedGroups(), ",");
			System.out.println("tokens --- "+token.countTokens());
			while(token.hasMoreTokens()){
				groupId = token.nextToken();
				
				try {
					System.out.println("group Id   - "+groupId);
					ps = con.prepareStatement(query);
					ps.setDouble(1, userId);
					ps.setDouble(2, Double.parseDouble(groupId));
					ps.executeUpdate();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}*/
		}
	 }
	 
	
	 
	public String updateUser(UserBean userBean) throws Exception {
		autoUserId=getAutoUserId();
        try
        {
        	System.out.println("Current List = "+userBean.getSelectedGroups());
        	
            con=lmDB.getDBConnection();
            pst1=con.prepareStatement("update user set JameInsanID=?, InsanID=?, Name=?, Adress=?, Block=?, District=?, State=?, Country=?, ContactNumber=?, RoleID=?, Status=? where UserID = ?");
            pst1.setLong(1,Long.valueOf(userBean.getJiid()));
            pst1.setLong(2,Long.valueOf(userBean.getInsanId()));
            pst1.setString(3,userBean.getName());
            pst1.setString(4,userBean.getAddress());
            pst1.setString(5,userBean.getBlock());
            pst1.setString(6,userBean.getDistrict());
            pst1.setString(7,userBean.getState());
            pst1.setString(8,userBean.getCountry());
            if(LocMgmtUtil.isNullOrBlankOrSpeltSo(userBean.getContactNumber()))
            	pst1.setLong(9, 0);
            else
            	pst1.setLong(9,Long.valueOf(userBean.getContactNumber()));
            pst1.setInt(10,Integer.valueOf(userBean.getRole()));
            pst1.setString(11,userBean.getStatus());
            pst1.setString(12,userBean.getUserId());
            pst1.executeUpdate();
        }
        catch(Exception e){e.printStackTrace();}
        finally{try{if(pst1!=null)pst1.close();if(rs1!=null)rs1.close();if(con!=null)con.close();}catch(SQLException ee){}}
        return ITSConstants.SUCCESS;
    }
	
	public String deleteUser(UserBean userBean) throws Exception {
		autoUserId=getAutoUserId();
        try
        {
            con=lmDB.getDBConnection();
            pst1=con.prepareStatement("update user set Status = ? where  UserID = ?");
            pst1.setString(1,ITSConstants.INACTIVE);
            pst1.setString(2,userBean.getUserId());
            pst1.executeUpdate();
        }
        catch(Exception e){e.printStackTrace();}
        finally{try{if(pst1!=null)pst1.close();if(rs1!=null)rs1.close();if(con!=null)con.close();}catch(SQLException ee){}}
        return ITSConstants.SUCCESS;
    }
	
	public List<UserBean> getUsersInDatabase()
    {
        List<UserBean> usersInDatabase = new ArrayList<UserBean>();
        UserBean userBean;
        try
        {
            con=lmDB.getDBConnection();
        	//con = ConnMngt.getConnection();
            pst1=con.prepareStatement("select UserID, JameInsanID, InsanID, Name, Adress, Block, District, State, Country, ContactNumber, RoleID, Status from user where Status = 'Active' order by UserID desc ");
            rs1 = pst1.executeQuery();
            while(rs1.next()) {                
            	userBean = new UserBean();
            	userBean.setUserId(rs1.getString("UserID"));
            	userBean.setJiid(rs1.getString("JameInsanID"));
            	userBean.setInsanId(rs1.getString("InsanID"));
            	userBean.setName(rs1.getString("Name"));
            	userBean.setAddress(rs1.getString("Adress"));
            	userBean.setBlock(rs1.getString("Block"));            	
            	userBean.setDistrict(rs1.getString("District"));
            	userBean.setState(rs1.getString("State"));
            	userBean.setCountry(rs1.getString("Country"));
            	userBean.setContactNumber(rs1.getString("ContactNumber"));
            	userBean.setRole(rs1.getString("RoleID"));
            	userBean.setStatus(rs1.getString("Status"));               	
                usersInDatabase.add(userBean);
            }            
        }
        catch(Exception e){e.printStackTrace();}
        finally{try{if(pst1!=null)pst1.close();if(rs1!=null)rs1.close();if(con!=null)con.close();}catch(SQLException ee){}}
        return usersInDatabase;
    }
	
	 public static void main(String[] args) {
		 System.out.println("main method");
		UserService service = new UserService();
		service.getUsersInDatabase();
	}
	
	 public List getGroupsForUser(List userList){
		 UserBean bean;
		 List groupList = null;
		 List<UserBean> beans = new ArrayList<UserBean>();
		 Iterator iterator = userList.iterator();
		 while(iterator.hasNext()){
			 bean = (UserBean) iterator.next();
			 String userId = bean.getUserId();
			 System.out.println("userId   "+userId);
			 try {
				 con=lmDB.getDBConnection();
				groupList= new ArrayList();
				pst1=con.prepareStatement("select GroupID from location_user where UserID=?");
				pst1.setString(1, userId);
	            rs1 = pst1.executeQuery();
	            while(rs1.next()) {            	
	            	groupList.add(rs1.getString("GroupID"));
	            }  
	            System.out.println("groupList  "+groupList);
				bean.setSelectedGroups(groupList);
				System.out.println("bean.getUserId( ) "+bean.getUserId());
				System.out.println(bean.getSelectedGroups());
			}  catch (SQLException e) {
				e.printStackTrace();
			}
			beans.add(bean);
		 }
		 
		 return beans;
	 }
	
	
}